-- @owner: opentestcase023
-- @date: 2022/02/24
-- @testpoint: 触发器结合带有多种操作判断函数验证

--step1:建表;expect:成功
drop table if exists t_trigger_src_00070 cascade;
drop table if exists t_trigger_des_00070 cascade;
create table t_trigger_src_00070(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des_00070(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:触发器结合带有多种操作判断函数验证;expect:成功
create or replace function f_ustore_trigger_case0070_a()
returns trigger as $$
begin
if(tg_op='DELETE')
then
delete from t_trigger_des_00070 where id2=2;
return new;
end if;
if(tg_op='INSERT')
then
insert into t_trigger_des_00070 values(new.id1,new.id2,new.id3);
return new;
end if;
if(tg_op='UPDATE')
then
update t_trigger_des_00070 set id1=2000 where id2=5;
return new;
end if;
return null;
end;
$$language plpgsql;
/
drop trigger if exists tri_ustore_trigger_case0070_a on t_trigger_src_00070;
create trigger tri_ustore_trigger_case0070_a after delete or insert or update on t_trigger_src_00070
for each row
execute procedure f_ustore_trigger_case0070_a();
/
drop sequence if exists seq_ustore_trigger_case0070;
create sequence seq_ustore_trigger_case0070;
create or replace function f_ustore_trigger_case0070_b()
returns trigger as $$
begin
if(tg_op='DELETE')
then
delete from t_trigger_des_00070 where id3=3;
return new;
end if;
if(tg_op='INSERT')
then
insert into t_trigger_des_00070 values(nextval('seq_ustore_trigger_case0070'),nextval('seq_ustore_trigger_case0070'),nextval('seq_ustore_trigger_case0070'));
return new;
end if;
if(tg_op='UPDATE')
then
update t_trigger_des_00070 set id1=3000;
return new;
end if;
return null;
end;
$$language plpgsql;
/
drop trigger if exists tri_ustore_trigger_case0070_b on t_trigger_src_00070;
create trigger tri_ustore_trigger_case0070_b before delete or insert or update on t_trigger_src_00070
for each row
execute procedure f_ustore_trigger_case0070_b();
/
alter trigger tri_ustore_trigger_case0070_b on t_trigger_src_00070 rename to atri_ustore_trigger_case0070_aa;
alter trigger tri_ustore_trigger_case0070_a on t_trigger_src_00070 rename to tri_ustore_trigger_case0070_b;
alter trigger atri_ustore_trigger_case0070_aa on t_trigger_src_00070 rename to tri_ustore_trigger_case0070_a;
insert into t_trigger_src_00070 values (1,1,1),(2,2,2),(3,3,3);
select * from t_trigger_src_00070 order by id1,id2;
select * from t_trigger_des_00070 order by id1,id2;
update t_trigger_src_00070 set id1=100;
select * from t_trigger_src_00070 order by id1,id2;
select * from t_trigger_des_00070 order by id1,id2;
delete from t_trigger_src_00070 where id1=100;
select * from t_trigger_src_00070 order by id1,id2;
select * from t_trigger_des_00070 order by id1,id2;

--step3:清理环境;expect:成功
drop trigger if exists tri_ustore_trigger_case0070_b on t_trigger_src_00070;
drop trigger if exists tri_ustore_trigger_case0070_a on t_trigger_src_00070;
drop sequence if exists seq_ustore_trigger_case0070;
drop table if exists t_trigger_src_00070 cascade;
drop table if exists t_trigger_des_00070 cascade;
drop function if exists f_ustore_trigger_case0070_a;
drop function if exists f_ustore_trigger_case0070_b;